﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using Ebiz.libs;
using System.Web.Services;
using System.Web.Script.Services;
using System.IO;
using System.Xml.Linq;
using System.Globalization;
using System.Web.UI.HtmlControls;
using System.Text;
using System.Net;

namespace Ebiz
{
    public partial class RequestList : System.Web.UI.Page
    {
        public string testMe = "";
        public string BtnStyle = "";
        public string qrySearch = "";
        public string tblContent = "";

        public string DistributorList = "";
        public enum ShowMode{
            _Requester, _Product, _Customer
        }
        public string TableContent = "";

        public string ShowTblStyle = "";
        public string ShowBtnStyle = "";
        public string BtnStyleReset = "";
        public string BtnStyleDL = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            DistributorList = getDistributor();
            if (!IsPostBack)
            {
                ShowBtnStyle = "display:none;";
                BtnStyleDL = "display:none;";
                //LoadRequestList();
                ShowList();
            }
        }

        protected static string getDistributor(){
            string result = "";

            string strCon = DBUtilities.strConnection("DevCon");
            SqlConnection sqlCon = new SqlConnection(strCon);
            using (sqlCon){
                sqlCon.Open();

                string strQuery = "SELECT DistributorCode, DistributorName FROM mstDistributor ORDER BY DistributorCode";
                SqlCommand sqlCmd = new SqlCommand(strQuery, sqlCon);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    while (sqlReader.Read()){
                        string strCode = "<option value=\"" + sqlReader.GetValue(0).ToString() + "\">";
                        string strName = sqlReader.GetValue(1).ToString() + "</option>\n";

                        string opt = strCode + strName;
                        result += opt;
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static Dictionary<string, object> ShowPopList(string ID, string Name,
            int StartIndex, ShowMode showMode){

            int SearchMode = 0;
            Dictionary<string, object> result = new Dictionary<string, object>();
            int TotalRows = 0;
            string TableName = "";
            string FieldName = "";
            string ProcedureName = "";
            switch (showMode){
                case ShowMode._Requester:
                    TableName = "UserAccounts";
                    FieldName = "UserName";
                    ProcedureName = "UserList";
                    SearchMode = 0;
                    break;
                case ShowMode._Product:
                    TableName = "v_mapProduct";
                    FieldName = "ProductName";
                    ProcedureName = "spProductList";
                    SearchMode = 1;
                    break;
                case ShowMode._Customer:
                    TableName = "v_mapCustomer";
                    FieldName = "CUSTNAME";
                    ProcedureName = "spCustomerList";
                    SearchMode = 2;
                    break;
                
            }

            string qry = "";
            List<string> SearchKey = new List<string>();
            if (!(showMode == ShowMode._Requester)) {
                if (string.IsNullOrEmpty(ID) && string.IsNullOrEmpty(Name)){
                    qry = "SELECT COUNT(DistributorCode) AS TotalRows FROM " + TableName;
                }else if(string.IsNullOrEmpty(ID) && !(string.IsNullOrEmpty(Name))){
                    qry = "SELECT COUNT(DistributorCode) AS TotalRows FROM " + TableName +
                        " WHERE (" + FieldName + " LIKE '%' + @" + FieldName + " + '%')";
                }else if(!(string.IsNullOrEmpty(ID)) && string.IsNullOrEmpty(Name)){
                    qry = "SELECT COUNT(DistributorCode) AS TotalRows FROM " + TableName +
                       " WHERE (DistributorCode = @DistributorCode)";
                }else{
                    qry = "SELECT COUNT(DistributorCode) AS TotalRows FROM " + TableName +
                        " WHERE (DistributorCode = @DistributorCode) AND (" +
                        FieldName + " LIKE '%' + @" + FieldName + " + '%')";
                }
            }else{
                if (string.IsNullOrEmpty(Name)){
                    qry = "SELECT COUNT(UserName) AS TotalRows FROM " + TableName + " WHERE " + 
                        "(IDRole NOT IN(1,2))";
                }else{
                    qry = "SELECT COUNT(UserName) AS TotalRows FROM " + TableName + " WHERE " +
                        "(IDRole NOT IN (1,2)) AND (FullName LIKE '%' + @" + FieldName + " + '%')";
                }
            }

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@DistributorCode", ID);
                    cmd.Parameters.AddWithValue("@" + FieldName, Name);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            TotalRows = Convert.ToInt32(rdr.GetValue(0).ToString());
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }

            if (TotalRows > 0){
                SearchKey.Add(ID);
                SearchKey.Add(Name);
            }
            result = GetDataList(StartIndex, TotalRows, ProcedureName, SearchKey, SearchMode);
            return result;
        }

        private static Dictionary<string, object> GetDataList(int StartIndex, int TotalRows,
            string ProcedurName, List<string> SearchKey, int SearchMode){

            Dictionary<string, object> result = new Dictionary<string, object>();

            int ShowRows = 15;
            int NumRows = TotalRows;
            int TPages = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(NumRows / ShowRows)));

            SqlConnection sqlCon = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (sqlCon){
                List<object> TblRows = new List<object>();
                int StartOffset = (StartIndex < 2) ? 0 : ((StartIndex * ShowRows) - ShowRows) + 1;
                int EndOffset = (StartIndex > 1) ? (ShowRows - 1) : ShowRows;

                if (NumRows > 0){
                    sqlCon.Open();
                    SqlCommand sqlCmd = new SqlCommand(ProcedurName, sqlCon);
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.Parameters.Add(new SqlParameter("Start", StartOffset));
                    sqlCmd.Parameters.Add(new SqlParameter("ShowRows", EndOffset));
                    if (SearchMode == 0){
                        if (string.IsNullOrEmpty(SearchKey[1]) == false) {
                            sqlCmd.Parameters.Add(new SqlParameter("Name", SearchKey[1]));
                        }
                    }else{
                        if(string.IsNullOrEmpty(SearchKey[0]) == false){
                            sqlCmd.Parameters.Add(new SqlParameter("ID", SearchKey[0]));
                        }
                        if (string.IsNullOrEmpty(SearchKey[1]) == false) {
                            sqlCmd.Parameters.Add(new SqlParameter("Name", SearchKey[1]));
                        }
                    }

                    SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
                    if (sqlRdr.HasRows){
                        int idx = 0;
                        while (sqlRdr.Read()){
                            string tmpResult1 = "";
                            string tmpResult2 = "";
                            switch (SearchMode) { 
                                case 0: case 1:
                                    tmpResult1 = sqlRdr.GetValue(1).ToString();
                                    tmpResult2 = sqlRdr.GetValue(2).ToString();
                                    break;
                                case 2:
                                    tmpResult1 = sqlRdr.GetValue(1).ToString();
                                    tmpResult2 = sqlRdr.GetValue(3).ToString();
                                    break;
                            }

                            Dictionary<string, object> tblRow = new Dictionary<string, object>();
                            tblRow.Add("ID", tmpResult1);
                            tblRow.Add("Name", tmpResult2);

                            TblRows.Add(tblRow);

                            idx++;
                        }
                    }
                    sqlCon.Close();

                    int ActivePage = (StartIndex == 0) ? 1 : StartIndex;
                    int PrevPage = (ActivePage > 1) ? (ActivePage - 1) : 0;
                    int NextPage = (ActivePage < TPages) ? (ActivePage + 1) : 0;
                    int TotalPages = (TPages > 1) ? TPages : 1;

                    result.Add("ShowRows", ShowRows);
                    result.Add("PrevPage", PrevPage);
                    result.Add("ActivePage", ActivePage);
                    result.Add("NextPage", NextPage);
                    result.Add("TotalPages", TotalPages);
                    result.Add("Content", TblRows);
                }
            }
            return result;
        }

        private void ShowList() {
            string _ReqStatus = hidStatus.Value.ToString();
            string _DateStart = hidDateStart.Value.ToString();
            string _DateEnd = hidDateEnd.Value.ToString();
            string _DateAll = hidPeriod.Value.ToString();
            string _Distributor = hidDistributor.Value.ToString();
            string _ReqName = hidReqName.Value.ToString();
            string _ReqProduct = hidReqProduct.Value.ToString();
            string _ReqCustomer = hidReqCustomer.Value.ToString();

            string ReqStatus = "";
            string Period = "";
            string Distributor = "";
            string ReqName = "";
            string ReqProduct = "";
            string ReqCustomer = "";
            
            Dictionary<string, object> dctParams = new Dictionary<string, object>();
            if (!(string.IsNullOrEmpty(_ReqStatus) || _ReqStatus == "0")) {
                //ReqStatus = " AND (tbl_Request.StatusCode = @StatusCode)";
                //dctParams.Add("@StatusCode", GetStatusCode(_ReqStatus));
                ReqStatus = " AND (tbl_Request.StatusCode IN " + GetStatusCode(_ReqStatus) + ")";
            }
            if (string.IsNullOrEmpty(_DateAll) || _DateAll == "0") {
                string DateStart = "";
                string DateEnd = "";
                if (!(string.IsNullOrEmpty(_DateStart) || _DateStart == "0")){
                    DateStart = " AND (tbl_Request.DateRequest >= (CONVERT(datetime,@DateStart,0)))";
                    dctParams.Add("@DateStart", Func.FormatDate(_DateStart, Func.DTFormat.DMYtoYMD, "-", "/"));
                }
                if (!(string.IsNullOrEmpty(_DateEnd) || _DateEnd == "0")) {
                    DateEnd = " AND(tbl_Request.DateRequest <= (CONVERT(datetime,@DateEnd,0)))";
                    dctParams.Add("@DateEnd", Func.FormatDate(_DateEnd, Func.DTFormat.DMYtoYMD, "-", "/"));
                }
                Period = DateStart + DateEnd;
            }
            if (!(string.IsNullOrEmpty(_Distributor) || _Distributor == "" || _Distributor == "0")) {
                Distributor = " AND (tbl_Request.DistributorCode = @DistributorCode)";
                dctParams.Add("@DistributorCode", _Distributor);
            }
            if (!(string.IsNullOrEmpty(_ReqName) || _ReqName == "0")) {
                string[] tmpReqName = _ReqName.Split('.');
                int ReqNameLen = tmpReqName.Length;
                if (ReqNameLen > 1){
                    ReqName = " AND (tbl_Request.CreatedBy IN (" + GetParamValues(_ReqName) + "))";
                }else{
                    ReqName = " AND (tbl_Request.CreatedBy = @Requesters)";
                    dctParams.Add("@Requesters", _ReqName);
                }
            }
            if (!(string.IsNullOrEmpty(_ReqProduct) || _ReqProduct == "0")) {
                string[] tmpReqProduct = _ReqProduct.Split(',');
                int ReqProductLen = tmpReqProduct.Length;
                if (ReqProductLen > 1) {
                    ReqProduct = " AND (tbl_RequestProduct.ProductCode IN (" + GetParamValues(_ReqProduct) + "))";
                }else{
                    ReqProduct = " AND (tbl_RequestProduct.ProductCode = @ProductCode)";
                    dctParams.Add("@ProductCode", _ReqProduct);
                }
            }
            if (!(string.IsNullOrEmpty(_ReqCustomer) || _ReqCustomer == "0")){
                string[] tmpReqCustomer = _ReqCustomer.Split(',');
                int ReqCustomerLen = tmpReqCustomer.Length;
                if (ReqCustomerLen > 1) {
                    ReqCustomer = " AND (tbl_RequestCustomer.CSCodeAZI IN (" + GetParamValues(_ReqCustomer) + "))";
                }else{
                    ReqCustomer = " AND (tbl_RequestCustomer.CSCodeAZI = @CustomerCode)";
                    dctParams.Add("@CustomerCode", _ReqCustomer);
                }
            }
            /*
            string QryWhere = "";
            int dctLen = dctParams.Count;
            */
            string QryWhere = ReqStatus + Period + Distributor + ReqName + ReqProduct + ReqCustomer;
            
            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT ROW_NUMBER() OVER (ORDER BY tbl_Request.DateRequest DESC, " +
            "tbl_Request.CLNum DESC) AS Num, " +
            "tbl_Request.CLNum, tbl_Request.CreatedBy AS UserID, ReqField.FullName AS Requester, " +
            "convert(varchar, tbl_Request.DateRequest, 105) AS DateRequest, " +
            "tbl_Request.DistributorCode, tbl_Request.CLCustName AS CustomerName, " +
            "(SELECT UserAccounts.FullName FROM " + dbName + ".dbo.UserAccounts " +
            "WHERE (UserAccounts.UserName = tbl_Request.ApprovalCurrent)) AS NextApproval, " +
            "mstStatusCode.Description AS CLStatus, tbl_Request.Token, tbl_Request.StatusCode  " +
            "FROM " + dbName + ".dbo.tbl_Request " +
            "INNER JOIN " + dbName + ".dbo.mstStatusCode ON mstStatusCode.StatusCode = tbl_Request.StatusCode " +
            "INNER JOIN " + dbName + ".dbo.UserAccounts AS ReqField ON ReqField.UserName = tbl_Request.CreatedBy " +
            "INNER JOIN " + dbName + ".dbo.tbl_RequestProduct ON tbl_RequestProduct.CLNum = tbl_Request.CLNum " +
            "INNER JOIN " + dbName + ".dbo.tbl_RequestCustomer ON tbl_RequestCustomer.CLNum = tbl_Request.CLNum " +
            " WHERE (tbl_Request.StatusCode IS NOT NULL)" + QryWhere +
            " GROUP BY tbl_Request.CLNum, tbl_Request.CreatedBy, tbl_Request.DateRequest, " +
            "ReqField.FullName, tbl_Request.DistributorCode, " +
            "tbl_Request.CLCustName, tbl_Request.ApprovalCurrent, mstStatusCode.Description, " +
            "tbl_Request.Token, tbl_Request.StatusCode";

            if (GetRows(QryWhere, dctParams) > 0) {
                ShowBtnStyle = "display:block;";
                BtnStyleDL = "display:block;";

                Dictionary<string, object> dctSYS = Func.GetSysConfig();
                string _Port = dctSYS["ServerPort"].ToString();
                //string SYSPort = (string.IsNullOrEmpty(_Port)) ? "/" : ":" + _Port + "/";
                string SYSPort = "/";
                string SYSUrl = dctSYS["ServerAddress"].ToString() + SYSPort;

                SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
                using (cn){
                    try{
                        cn.Open();

                        SqlCommand cmd = new SqlCommand(qry, cn);
                        foreach (KeyValuePair<string, object> kvp in dctParams){
                            cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
                        }
                        SqlDataReader rdr = cmd.ExecuteReader();        
                        if (rdr.HasRows){
                            int idx = 0;
                            while (rdr.Read()){
                                string TD_Class = ((idx % 2) == 0) ? " class=\"odd\"" : " class=\"even\"";
                                
                                string TD_CLNumber = rdr.GetValue(1).ToString();
                                string TD_Requester = rdr.GetValue(3).ToString();
                                string TD_DateRequest = rdr.GetValue(4).ToString();
                                string TD_Distributor = rdr.GetValue(5).ToString();
                                string TD_Customer = rdr.GetValue(6).ToString();
                                string tmp_NextApv = rdr.GetValue(7).ToString();
                                string TD_Status = rdr.GetValue(8).ToString();
                                string Token = rdr.GetValue(9).ToString();
                                string tmpStatus = rdr.GetValue(10).ToString();

                                string tmpPage = (tmpStatus == "110" || tmpStatus == "111" || tmpStatus == "210" || tmpStatus == "211" || tmpStatus == "213" || tmpStatus == "214") ? "BulkView" : "ViewRequest";
                                string PageQry = "id=" + TD_CLNumber + "&atdn=" + Token;
                                string PageLink = tmpPage + ".aspx?cl=" + CryptLib.Base64(PageQry, CryptMode.ENCRYPT);
                                //string PageLink = "ViewRequest.aspx?cl=" + CryptLib.Base64(PageQry, CryptMode.ENCRYPT);

                                string TD_NextApv = (tmpStatus == "200" || tmpStatus == "210") ? tmp_NextApv : "";
                                string tmpContent = "<tr " + TD_Class + ">";
                                tmpContent += "<td>" + (idx + 1) + "</td>";
                                tmpContent += "<td style=\"text-align:left;\">" + TD_Requester + "</td>";
                                tmpContent += "<td>" + TD_DateRequest + "</td>";
                                tmpContent += "<td><a href=\"" + PageLink + "\" style=\"text-decoration:none;\">" + TD_CLNumber + "</a></td>";
                                tmpContent += "<td>" + TD_Distributor + "</td>";
                                tmpContent += "<td style=\"text-align:left;\">" + TD_Customer + "</td>";
                                tmpContent += "<td style=\"text-align:left;\">" + TD_NextApv + "</td>";
                                tmpContent += "<td>" + TD_Status + "</td>";
                                tmpContent += "</tr>";

                                tblContent += tmpContent;
                                idx++;
                            }
                        }
                    }catch (Exception ex){
                        string errMsg = ex.Message;
                    }finally{
                        if (cn.State == ConnectionState.Open){
                            cn.Close();
                        }
                    }
                }
            }else{
                ShowBtnStyle = "display:block;";
                BtnStyleDL = "display:none;";
                tblContent = "<tr><td colspan=\"8\" style=\"text-align:center;\">No Data Found</td></tr>";
            }
            
        }

        private static int GetRows(string queryWhere, Dictionary<string, object> dctParameters) {
            int result = 0;

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT COUNT(DISTINCT tbl_Request.CLNum) AS RowNum " +
            "FROM " + dbName + ".dbo.tbl_Request " +
            "INNER JOIN " + dbName + ".dbo.tbl_RequestProduct ON tbl_RequestProduct.CLNum = tbl_Request.CLNum " +
            "INNER JOIN " + dbName + ".dbo.tbl_RequestCustomer ON tbl_RequestCustomer.CLNum = tbl_Request.CLNum " +
            " WHERE (tbl_Request.StatusCode IS NOT NULL)" + queryWhere +
            " GROUP BY tbl_Request.CLNum, tbl_RequestProduct.CLNum, tbl_RequestCustomer.CLNum";
            
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    foreach (KeyValuePair<string, object> kvp in dctParameters){
                        cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
                    }
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            result = Convert.ToInt32(rdr.GetValue(0).ToString());
                        }
                    }
                }
                catch (Exception ex) {
                    string errMsg = ex.Message;
                }
                finally {
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string GetStatusCode(string StatusCode) {
            string result = "";
            switch (StatusCode) { 
                case "1":
                    result = "('200','210')";
                    break;
                case "2":
                    result = "('201','211')";
                    break;
                case "3":
                    result = "('203','213')";
                    break;
                case "4":
                    result = "('204','214')";
                    break;
            }
            return result;
        }

        private static string GetRequesterQuery(string Requester){
            string result = "";
            string[] ReqList = Requester.Split(',');
            int ReqCount = ReqList.Length;
            if (ReqCount > 1) {
                result = " AND (tbl_Request.CreatedBy IN (@Requesters))";
            } else {
                result = " AND (tbl_Request.CreatedBy = @Requesters)";
            }
            return result;
        }

        private static string GetProductQuery(string Product) {
            string result = "";
            string[] ReqList = Product.Split(',');
            int ReqCount = ReqList.Length;
            if (ReqCount > 1){
                result = " AND (tbl_RequestProduct.ProductCode IN (@Products))";
            }else{
                result = " AND (tbl_RequestProduct.ProductCode = @Products)";
            }
            return result;
        }

        private static string GetCustomerQuery(string Customer) {
            string result = "";
            string[] ReqList = Customer.Split(',');
            int ReqCount = ReqList.Length;
            if (ReqCount > 1){
                result = " AND (tbl_RequestCustomer.CSCodeAZI IN (@Customers))";
            }else{
                result = " AND (tbl_RequestCustomer.CSCodeAZI = @Customers)";
            }
            return result;
        }

        private static string GetParamValues(string InputValues){
            string result = "";
            string[] ReqList = InputValues.Split(',');
            int ReqCount = ReqList.Length;
            if (ReqCount > 1){
                int idx = 0;
                foreach (string strVals in ReqList){
                    idx++;
                    string tmpResult = (idx < ReqCount) ?  "'" + strVals + "'," : "'" + strVals + "'";
                    //result += "'" + strVals + "'";
                    result += tmpResult;
                }
            }else{
                result = InputValues;
            }

            return result;
        }

        private static string GetReqFullName(string Requester) {
            string result = "";
            string qry = "SELECT FullName FROM UserAccounts WHERE " +
                "(UserName = @UserName)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@UserName", Requester);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch(Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if(cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string ShowStatusCode(string StatusCode) {
            string result = "";
            switch (StatusCode) { 
                case "200":
                    result = "Waiting for Approval";
                    break;
                case "201":
                    result = "Approved";
                    break;
                case "203":
                    result = "Return for Information";
                    break;
                case "204":
                    result = "Rejected";
                    break;
                case "210":
                    result = "Waiting for Approval (Bulk)";
                    break;
                case "211":
                    result = "Approved (Bulk)";
                    break;
                case "213":
                    result = "Return for Information (Bulk)";
                    break;
                case "214":
                    result = "Rejected (Bulk)";
                    break;
            }
            return result;
        }
        
        protected void btnSearchCL_Click(object sender, EventArgs e){
            ShowList();
            ShowTblStyle = "display:none;";
        }
    }
}
